import mysql.connector
import openpyxl

# mydb = mysql.connector.connect(
#     host="172.32.148.68",  # 数据库主机地址
#     user="hebeitower",  # 数据库用户名
#     port=3306,
#     database="hebei-tower",  # 数据库名称 没有的话插入操作报错
#     passwd="Hebeitower1!",  # 数据库密码
# )

mydb = mysql.connector.connect(
    host="121.36.64.108",  # 数据库主机地址
    user="hebei",  # 数据库用户名
    port=8181,
    database="hebei-tower",  # 数据库名称 没有的话插入操作报错
    passwd="Hebeitower1!",  # 数据库密码
)

# 根据配置信息生成操作对象
mycursor = mydb.cursor()
# 定义要执行的sql %s为占位符
sql = "INSERT INTO bs_pcms_t_pcm_contract (contract_id,contract_code,contract_name,old_contract_code,contract_type,contract_state,contract_attr,contract_sign_num,contract_sign_date,contract_begin_date_original,contract_begin_date,contract_end_date,deal_staff_id,deal_staff_name,deal_org_id,deal_org_name,contract_amount,is_auto_continue,has_deposit,owner_id,owner_name,org_id,org_name,author_id,create_date,state_date,mod_author,old_contract_id,field_id,pro_orgcode,city_orgcode,is_agent_hold,change_reason,start_pay_date,operator,renew_contract_code,renew_is_need,relevance_no,is_relevance,buss_category,contract_type_other,is_pay_type,is_special_invoice,change_contract_amount,sum_date) VALUES(" \
      "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s" \
      ")";
#sql = "INSERT INTO hb_ai_pre_table (pre_table_id,table_name,cols) VALUES (%s,%s,%s)"
2

sheet = workbook["Sheet"]
#sheet = workbook["hb_ai_pre_table"]

count = 0
for one_column_data in sheet.iter_rows():
    if count == 0:
        count = count + 1
        continue
    row_data = []
    for cell in one_column_data:
        row_data.append(cell.value)
        # print(cell.value)
    val = tuple(row_data)
    try:
        print(sql)
        mycursor.execute(sql, val)
        mydb.commit()
    except exa:
        print(exa)
        print("出错")
    print(val)
